/******************************************************************************
Input:			RAW CSV Files
Output:			RAW Stata Files
Description: This file converts all the txt files to stata format. It also standardizes the
Student ID to omit any leading zeros: e.g "012345" -> "12345"
********************************************************************************/

clear all

* Switches
// Match Files

	local hs_match		1
	local lotto_nrs		1
	local target		1
	local edopt			1

// Demographics 
	local demographics 	1

// Outcomes
	local ny_test_g38	1
	local sat			1

* Start Cleaning

/*****************************************************************************

   Match Files

 ****************************************************************************/
// High School Match

if `hs_match' == 1 {

	forval year = 2005/2016 {

		local digits_plus = `year' - 2000 + 1
		if length("`digits_plus'") == 1 local digits_plus 0`digits_plus'
		import delimited using "${rawdata}NYCDOE/HSAPS/`year'-`digits_plus'_HSAPS_Scrambled.csv",  clear  delimiters(",") stringcols(_all) // sheet("Summary")
		eliminate_leading_0
     	destring _all, replace
		replace feeder_school_name=strtrim(feeder_school_name)
		compress
		save "${cleandata}match/HSAPS/`year'-`digits_plus'_HSAPS_Scrambled.dta", replace
	}
}

// Lottery Numbers

if `lotto_nrs' == 1 {

	forval year = 2012/2016 {

		import delimited using "${rawdata}NYCDOE/LotteryNumbers/`year'_lottery.csv",  clear  delimiters(",") stringcols(_all) // sheet("Summary")
		eliminate_leading_0
		compress
		save "${cleandata}match/LotteryNumbers/`year'_lottery.dta", replace

	}
}

// Match Targets

if `target' == 1 {

		import excel using "${rawdata}NYCDOE/MatchTargets/match_targets.xlsx",  clear  firstrow // sheet("Summary")
		compress
		save "${cleandata}match/MatchTargets/match_targets.dta", replace

	}

// EdOpt Matches

if `edopt' == 1 {

		import delimited using "${rawdata}NYCDOE/EdoptMatches/EdOpt_Matches.csv",  clear  delimiters(",") stringcols(_all) // sheet("Summary")
		eliminate_leading_0
		compress
		save "${cleandata}match/EdoptMatches/EdOpt_Matches.dta", replace

	}

/*****************************************************************************

   Demographics / Enrollment

 ****************************************************************************/

// Demographics
if `demographics' == 1 {

	forval year = 2005/2019 {

		local digits_plus = `year' - 2000 + 1
		if length("`digits_plus'") == 1 local digits_plus 0`digits_plus'
		import delimited using "${rawdata}NYCDOE/JuneBiog/`year'-`digits_plus'_June-Biog_PK-12_Scrambled.csv",  clear  delimiters(",") stringcols(_all) // sheet("Summary")

		eliminate_leading_0
		compress
		save "${cleandata}demos/JuneBiog/`year'-`digits_plus'_June-Biog_PK-12_Scrambled.dta", replace

	}

}

/*****************************************************************************

  OUTCOMES

 ****************************************************************************/

// New York Grade 3-8 Assessments
if `ny_test_g38' == 1 {
	forval year = 2005/2016 {

		local digits_plus = `year' - 2000 + 1
		if length("`digits_plus'") == 1 local digits_plus 0`digits_plus'
		import delimited using "${rawdata}NYCDOE/TestBiog/`year'-`digits_plus'_Student_test-Biog_All_G38_NYC_Scrambled.csv",  clear  delimiters(",") stringcols(_all) // sheet("Summary")
		eliminate_leading_0
		compress
		save "${cleandata}outcomes/TestBiog/`year'-`digits_plus'_Student_test-Biog_All_G38_NYC_Scrambled.dta", replace

	}
}

// SAT
if `sat' == 1 {
	forval year = 2006/2016 {

		local digits_plus = `year' - 2000 + 1
		if length("`digits_plus'") == 1 local digits_plus 0`digits_plus'
		if `year' <= 2015 {
			import delimited using "${rawdata}NYCDOE/SAT/`year'-`digits_plus'_SAT_Admin_Scrambled.csv",  clear  delimiters(",") stringcols(_all) // sheet("Summary")
			eliminate_leading_0
			compress
			save "${cleandata}outcomes/SAT/`year'-`digits_plus'_SAT_Admin_Scrambled.dta", replace
		}
		if `year' >= 2015 {
			import delimited using "${rawdata}NYCDOE/SAT/`year'-`digits_plus'_SAT_Admin_New_Scrambled.csv",  clear  delimiters(",") stringcols(_all) // sheet("Summary")
			eliminate_leading_0
			compress
			save "${cleandata}outcomes/SAT/`year'-`digits_plus'_SAT_Admin_New_Scrambled.dta", replace
		}
	}
}
